﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PrePurchaseRequestDAL : IDALPrePurchaseRequest
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PrePurchaseRequestDAL()
        {

        }

        public IList<PrePurchaseRequestInfo> GetPrePurchaseRequest()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT ppr.*"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON ppr.RECORD_STATUS = rs.RECORD_STATUS"
                        + " WHERE ppr.icm_ppr_type_seq = 1 and ppr.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        //SysPprPriorityInfo infoPprPriority = new SysPprPriorityInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                        //SysPprMethodInfo infoPprMethod = new SysPprMethodInfo();
                        SysPprForInfo infoPprFor = new SysPprForInfo();
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmJobSiteInfo infoJobSiteDirect = new HrmJobSiteInfo();
                        FimMoneyRequestInfo infoMoneyRequest = new FimMoneyRequestInfo();
                        HrmEmployeeInfo infoEmployeeRequest = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSiteRequest = new HrmJobSiteInfo();
                        HrmEmployeeInfo infoEmployeeConfirmed = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmployeeApproved = new HrmEmployeeInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                        SysProgressInfo infoProgress = new SysProgressInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_SEQ"]);
                        info.Ref_no = (dr[i]["ICM_PPR_REF_NO"]).ToString();
                        info.Name = (dr[i]["ICM_PPR_NAME"]).ToString();
                        info.Reason = (dr[i]["ICM_PPR_REASON"]).ToString();
                        info.Year = (Convert.IsDBNull(dr[i]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[i]["BGM_YEAR"]));
                        info.DateRequested = (Convert.IsDBNull(dr[i]["REQUEST_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUEST_DATE"]));
                        info.DateConfirmed = (Convert.IsDBNull(dr[i]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["CONFIRM_DATE"]));
                        info.DateApproved = (Convert.IsDBNull(dr[i]["APPROVED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["APPROVED_WHEN"]));
                        info.DateRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUIRE_DATE"]));
                        info.DurationRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[i]["REQUIRE_DURATION"]));
                        //infoPprPriority.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_PRIOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_PRIOR_SEQ"]));
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_TYPE_SEQ"]));
                        //infoPprMethod.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_METHOD_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_METHOD_SEQ"]));
                        infoPprFor.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_FOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_FOR_SEQ"]));
                        infoPurchaseType.Seq = (Convert.IsDBNull(dr[i]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PC_TYPE_SEQ"]));
                        infoPurchaseType.Name = (dr[i]["ICM_PC_TYPE_NAME"]).ToString();
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["YEAR_TYPE_SEQ"]));
                        infoJobSite.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_SEQ"]));
                        infoJobSiteDirect.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_DIRECT_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_DIRECT_SEQ"]));
                        //infoMoneyRequest.Seq = (Convert.IsDBNull(dr[i]["MONEY_REQ_SEQ"]) ? null : (Nullable<int>)(dr[i]["MONEY_REQ_SEQ"]));
                        infoEmployeeRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_EMP_SEQ"]));
                        infoJobSiteRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_JOB_SITE_SEQ"]));
                        infoEmployeeConfirmed.Seq = (Convert.IsDBNull(dr[i]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["CONFIRM_EMP_SEQ"]));
                        //infoEmployeeApproved.Seq = (Convert.IsDBNull(dr[i]["APPROVED_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["APPROVED_EMP_SEQ"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_COUNT_SEQ"]));
                        infoProgress.Seq = (Convert.IsDBNull(dr[i]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PROG_SEQ"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoStatus.Name = (dr[i]["RECORD_STATUS_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        //info.InfoPprPriority = infoPprPriority; 
                        info.InfoPprType = infoPprType;
                        //info.InfoPprMethod = infoPprMethod; 
                        info.InfoPprFor = infoPprFor;
                        info.InfoPurchaseType = infoPurchaseType; 
                        info.InfoYearType = infoYearType; 
                        info.InfoJobSite = infoJobSite; 
                        info.InfoJobSiteDirect = infoJobSiteDirect; 
                        info.InfoMoneyRequest = infoMoneyRequest; 
                        info.InfoEmployeeRequest = infoEmployeeRequest; 
                        info.InfoJobSiteRequest = infoJobSiteRequest; 
                        info.InfoEmployeeConfirmed = infoEmployeeConfirmed; 
                        info.InfoEmployeeApproved = infoEmployeeApproved; 
                        info.InfoDurationType = infoDurationType; 
                        info.InfoDurationCount = infoDurationCount;
                        info.InfoProgress = infoProgress; 
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestInfo> GetPrePurchaseRequest(int yearTypeSeq, int year, Nullable<int> JobSiteSeq, Nullable<int> PcType, Nullable<int> ProgressSeq,string StatusSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@icm_pc_type_seq", PcType);
                dbManager.AddParameters(2, "@bgm_year", year);
                dbManager.AddParameters(3, "@job_site_seq", JobSiteSeq);
                dbManager.AddParameters(4, "@year_type_seq", yearTypeSeq);
                dbManager.AddParameters(5, "@icm_prog_seq", ProgressSeq);
                dbManager.AddParameters(6, "@record_status", StatusSeq);

                strSQL = "SELECT ppr.*"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                            + ", CAST(CONCAT(yt.YEAR_TYPE_NAME,' ', ppr.BGM_YEAR) AS CHAR) AS YEAR_TYPE_DESCR"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON ppr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON ppr.RECORD_STATUS = rs.RECORD_STATUS"
                        + " WHERE ppr.icm_ppr_type_seq = 1 and ppr.year_type_seq = @year_type_seq and ppr.bgm_year = @bgm_year and ppr.RECORD_STATUS <> @delete_status ";
                strSQL += (PcType != null) ? " and ppr.icm_pc_type_seq = @icm_pc_type_seq " : "";
                strSQL += (JobSiteSeq != null) ? " and job_site_seq = @job_site_seq " : "";
                strSQL += (ProgressSeq != null) ? " and icm_prog_seq = @icm_prog_seq " : "";
                strSQL += (StatusSeq != "") ? " and record_status = @record_status " : "";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        //SysPprPriorityInfo infoPprPriority = new SysPprPriorityInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                        //SysPprMethodInfo infoPprMethod = new SysPprMethodInfo();
                        SysPprForInfo infoPprFor = new SysPprForInfo();
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmJobSiteInfo infoJobSiteDirect = new HrmJobSiteInfo();
                        FimMoneyRequestInfo infoMoneyRequest = new FimMoneyRequestInfo();
                        HrmEmployeeInfo infoEmployeeRequest = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSiteRequest = new HrmJobSiteInfo();
                        HrmEmployeeInfo infoEmployeeConfirmed = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmployeeApproved = new HrmEmployeeInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                        SysProgressInfo infoProgress = new SysProgressInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_SEQ"]);
                        info.Ref_no = (dr[i]["ICM_PPR_REF_NO"]).ToString();
                        info.Name = (dr[i]["ICM_PPR_NAME"]).ToString();
                        info.Reason = (dr[i]["ICM_PPR_REASON"]).ToString();
                        info.YearTypeDescr = (dr[i]["YEAR_TYPE_DESCR"]).ToString();
                        info.Year = (Convert.IsDBNull(dr[i]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[i]["BGM_YEAR"]));
                        info.DateRequested = (Convert.IsDBNull(dr[i]["REQUEST_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUEST_DATE"]));
                        info.DateRequestedShow = String.Format("{0:dd/MM/yyyy}", info.DateRequested);
                        info.DateConfirmed = (Convert.IsDBNull(dr[i]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["CONFIRM_DATE"]));
                        info.DateApproved = (Convert.IsDBNull(dr[i]["APPROVED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["APPROVED_WHEN"]));
                        info.DateRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUIRE_DATE"]));
                        info.DurationRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[i]["REQUIRE_DURATION"]));
                        //infoPprPriority.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_PRIOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_PRIOR_SEQ"]));
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_TYPE_SEQ"]));
                        //infoPprMethod.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_METHOD_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_METHOD_SEQ"]));
                        infoPprFor.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_FOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_FOR_SEQ"]));
                        infoPurchaseType.Seq = (Convert.IsDBNull(dr[i]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PC_TYPE_SEQ"]));
                        infoPurchaseType.Name = (dr[i]["ICM_PC_TYPE_NAME"]).ToString();
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["YEAR_TYPE_SEQ"]));
                        infoJobSite.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_SEQ"]));
                        infoJobSiteDirect.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_DIRECT_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_DIRECT_SEQ"]));
                        //infoMoneyRequest.Seq = (Convert.IsDBNull(dr[i]["MONEY_REQ_SEQ"]) ? null : (Nullable<int>)(dr[i]["MONEY_REQ_SEQ"]));
                        infoEmployeeRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_EMP_SEQ"]));
                        infoJobSiteRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_JOB_SITE_SEQ"]));
                        infoEmployeeConfirmed.Seq = (Convert.IsDBNull(dr[i]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["CONFIRM_EMP_SEQ"]));
                        //infoEmployeeApproved.Seq = (Convert.IsDBNull(dr[i]["APPROVED_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["APPROVED_EMP_SEQ"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_COUNT_SEQ"]));
                        infoProgress.Seq = (Convert.IsDBNull(dr[i]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PROG_SEQ"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoStatus.Name = (dr[i]["RECORD_STATUS_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        //info.InfoPprPriority = infoPprPriority; 
                        info.InfoPprType = infoPprType;
                        //info.InfoPprMethod = infoPprMethod; 
                        info.InfoPprFor = infoPprFor;
                        info.InfoPurchaseType = infoPurchaseType;
                        info.InfoYearType = infoYearType;
                        info.InfoJobSite = infoJobSite;
                        info.InfoJobSiteDirect = infoJobSiteDirect;
                        info.InfoMoneyRequest = infoMoneyRequest;
                        info.InfoEmployeeRequest = infoEmployeeRequest;
                        info.InfoJobSiteRequest = infoJobSiteRequest;
                        info.InfoEmployeeConfirmed = infoEmployeeConfirmed;
                        info.InfoEmployeeApproved = infoEmployeeApproved;
                        info.InfoDurationType = infoDurationType;
                        info.InfoDurationCount = infoDurationCount;
                        info.InfoProgress = infoProgress;
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestInfo> GetPrePurchaseRequestUrgent()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT ppr.*"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON ppr.RECORD_STATUS = rs.RECORD_STATUS"
                        + " WHERE ppr.icm_ppr_type_seq = 2 and ppr.RECORD_STATUS <> @delete_status";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        //SysPprPriorityInfo infoPprPriority = new SysPprPriorityInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                        //SysPprMethodInfo infoPprMethod = new SysPprMethodInfo();
                        SysPprForInfo infoPprFor = new SysPprForInfo();
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmJobSiteInfo infoJobSiteDirect = new HrmJobSiteInfo();
                        FimMoneyRequestInfo infoMoneyRequest = new FimMoneyRequestInfo();
                        HrmEmployeeInfo infoEmployeeRequest = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSiteRequest = new HrmJobSiteInfo();
                        HrmEmployeeInfo infoEmployeeConfirmed = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmployeeApproved = new HrmEmployeeInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                        SysProgressInfo infoProgress = new SysProgressInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_SEQ"]);
                        info.Ref_no = (dr[i]["ICM_PPR_REF_NO"]).ToString();
                        info.Name = (dr[i]["ICM_PPR_NAME"]).ToString();
                        info.Reason = (dr[i]["ICM_PPR_REASON"]).ToString();
                        info.Year = (Convert.IsDBNull(dr[i]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[i]["BGM_YEAR"]));
                        info.DateRequested = (Convert.IsDBNull(dr[i]["REQUEST_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUEST_DATE"]));
                        info.DateConfirmed = (Convert.IsDBNull(dr[i]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["CONFIRM_DATE"]));
                        info.DateApproved = (Convert.IsDBNull(dr[i]["APPROVED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["APPROVED_WHEN"]));
                        info.DateRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUIRE_DATE"]));
                        info.DurationRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[i]["REQUIRE_DURATION"]));
                        //infoPprPriority.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_PRIOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_PRIOR_SEQ"]));
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_TYPE_SEQ"]));
                        //infoPprMethod.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_METHOD_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_METHOD_SEQ"]));
                        infoPprFor.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_FOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_FOR_SEQ"]));
                        infoPurchaseType.Seq = (Convert.IsDBNull(dr[i]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PC_TYPE_SEQ"]));
                        infoPurchaseType.Name = (dr[i]["ICM_PC_TYPE_NAME"]).ToString();
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["YEAR_TYPE_SEQ"]));
                        infoJobSite.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_SEQ"]));
                        infoJobSiteDirect.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_DIRECT_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_DIRECT_SEQ"]));
                        //infoMoneyRequest.Seq = (Convert.IsDBNull(dr[i]["MONEY_REQ_SEQ"]) ? null : (Nullable<int>)(dr[i]["MONEY_REQ_SEQ"]));
                        infoEmployeeRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_EMP_SEQ"]));
                        infoJobSiteRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_JOB_SITE_SEQ"]));
                        infoEmployeeConfirmed.Seq = (Convert.IsDBNull(dr[i]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["CONFIRM_EMP_SEQ"]));
                        //infoEmployeeApproved.Seq = (Convert.IsDBNull(dr[i]["APPROVED_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["APPROVED_EMP_SEQ"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_COUNT_SEQ"]));
                        infoProgress.Seq = (Convert.IsDBNull(dr[i]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PROG_SEQ"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoStatus.Name = (dr[i]["RECORD_STATUS_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        //info.InfoPprPriority = infoPprPriority; 
                        info.InfoPprType = infoPprType;
                        //info.InfoPprMethod = infoPprMethod; 
                        info.InfoPprFor = infoPprFor;
                        info.InfoPurchaseType = infoPurchaseType;
                        info.InfoYearType = infoYearType;
                        info.InfoJobSite = infoJobSite;
                        info.InfoJobSiteDirect = infoJobSiteDirect;
                        info.InfoMoneyRequest = infoMoneyRequest;
                        info.InfoEmployeeRequest = infoEmployeeRequest;
                        info.InfoJobSiteRequest = infoJobSiteRequest;
                        info.InfoEmployeeConfirmed = infoEmployeeConfirmed;
                        info.InfoEmployeeApproved = infoEmployeeApproved;
                        info.InfoDurationType = infoDurationType;
                        info.InfoDurationCount = infoDurationCount;
                        info.InfoProgress = infoProgress;
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestInfo> GetPrePurchaseRequestUrgent(int yearTypeSeq, int year, Nullable<int> JobSiteSeq, Nullable<int> ProgressSeq, string StatusSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@year_type_seq", yearTypeSeq);
                dbManager.AddParameters(2, "@bgm_year", year);
                dbManager.AddParameters(3, "@job_site_seq", JobSiteSeq);
                dbManager.AddParameters(4, "@icm_prog_seq", ProgressSeq);
                dbManager.AddParameters(5, "@record_status", StatusSeq);

                strSQL = "SELECT ppr.*"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                            + ", CAST(CONCAT(yt.YEAR_TYPE_NAME,' ', ppr.BGM_YEAR) AS CHAR) AS YEAR_TYPE_DESCR"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON ppr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON ppr.RECORD_STATUS = rs.RECORD_STATUS"
                        + " WHERE ppr.icm_ppr_type_seq = 2 and ppr.year_type_seq = @year_type_seq and ppr.bgm_year = @bgm_year and ppr.RECORD_STATUS <> @delete_status ";
                strSQL += (JobSiteSeq != null) ? " and job_site_seq = @job_site_seq " : "";
                strSQL += (ProgressSeq != null) ? " and icm_prog_seq = @icm_prog_seq " : "";
                strSQL += (StatusSeq != "") ? " and record_status = @record_status " : "";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        //SysPprPriorityInfo infoPprPriority = new SysPprPriorityInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                        //SysPprMethodInfo infoPprMethod = new SysPprMethodInfo();
                        SysPprForInfo infoPprFor = new SysPprForInfo();
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmJobSiteInfo infoJobSiteDirect = new HrmJobSiteInfo();
                        FimMoneyRequestInfo infoMoneyRequest = new FimMoneyRequestInfo();
                        HrmEmployeeInfo infoEmployeeRequest = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSiteRequest = new HrmJobSiteInfo();
                        HrmEmployeeInfo infoEmployeeConfirmed = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmployeeApproved = new HrmEmployeeInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                        SysProgressInfo infoProgress = new SysProgressInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_SEQ"]);
                        info.Ref_no = (dr[i]["ICM_PPR_REF_NO"]).ToString();
                        info.Name = (dr[i]["ICM_PPR_NAME"]).ToString();
                        info.Reason = (dr[i]["ICM_PPR_REASON"]).ToString();
                        info.YearTypeDescr = (dr[i]["YEAR_TYPE_DESCR"]).ToString();
                        info.Year = (Convert.IsDBNull(dr[i]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[i]["BGM_YEAR"]));
                        info.DateRequested = (Convert.IsDBNull(dr[i]["REQUEST_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUEST_DATE"]));
                        info.DateRequestedShow = String.Format("{0:dd/MM/yyyy}", info.DateRequested);
                        info.DateConfirmed = (Convert.IsDBNull(dr[i]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["CONFIRM_DATE"]));
                        info.DateApproved = (Convert.IsDBNull(dr[i]["APPROVED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["APPROVED_WHEN"]));
                        info.DateRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUIRE_DATE"]));
                        info.DurationRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[i]["REQUIRE_DURATION"]));
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_TYPE_SEQ"]));
                        infoPprFor.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_FOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_FOR_SEQ"]));
                        infoPurchaseType.Seq = (Convert.IsDBNull(dr[i]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PC_TYPE_SEQ"]));
                        infoPurchaseType.Name = (dr[i]["ICM_PC_TYPE_NAME"]).ToString();
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["YEAR_TYPE_SEQ"]));
                        infoJobSite.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_SEQ"]));
                        infoJobSiteDirect.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_DIRECT_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_DIRECT_SEQ"]));
                        infoEmployeeRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_EMP_SEQ"]));
                        infoJobSiteRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_JOB_SITE_SEQ"]));
                        infoEmployeeConfirmed.Seq = (Convert.IsDBNull(dr[i]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["CONFIRM_EMP_SEQ"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_COUNT_SEQ"]));
                        infoProgress.Seq = (Convert.IsDBNull(dr[i]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PROG_SEQ"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoStatus.Name = (dr[i]["RECORD_STATUS_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        //info.InfoPprPriority = infoPprPriority; 
                        info.InfoPprType = infoPprType;
                        //info.InfoPprMethod = infoPprMethod; 
                        info.InfoPprFor = infoPprFor;
                        info.InfoPurchaseType = infoPurchaseType;
                        info.InfoYearType = infoYearType;
                        info.InfoJobSite = infoJobSite;
                        info.InfoJobSiteDirect = infoJobSiteDirect;
                        info.InfoMoneyRequest = infoMoneyRequest;
                        info.InfoEmployeeRequest = infoEmployeeRequest;
                        info.InfoJobSiteRequest = infoJobSiteRequest;
                        info.InfoEmployeeConfirmed = infoEmployeeConfirmed;
                        info.InfoEmployeeApproved = infoEmployeeApproved;
                        info.InfoDurationType = infoDurationType;
                        info.InfoDurationCount = infoDurationCount;
                        info.InfoProgress = infoProgress;
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestInfo> GetPrePurchaseRequest(int year_type_seq, int year, Nullable<int> job_site_seq, Nullable<int> pc_type_seq, Nullable<int> ppr_type_seq, Nullable<int> ppr_method_seq, Nullable<int> prog_seq, string record_status)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@year_type_seq", year_type_seq);
                dbManager.AddParameters(1, "@year", year);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(3, "@pc_type_seq", pc_type_seq);
                dbManager.AddParameters(4, "@ppr_type_seq", ppr_type_seq);
                dbManager.AddParameters(5, "@ppr_method_seq", ppr_method_seq);
                dbManager.AddParameters(6, "@prog_seq", prog_seq);
                dbManager.AddParameters(7, "@record_status", record_status);
                dbManager.AddParameters(8, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT ppr.*"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                            + ", CAST(CONCAT(yt.YEAR_TYPE_NAME,' ', ppr.BGM_YEAR) AS CHAR) AS YEAR_TYPE_DESCR"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON ppr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON ppr.RECORD_STATUS = rs.RECORD_STATUS"
                        + " WHERE ppr.YEAR_TYPE_SEQ = @year_type_seq"
                            + " AND ppr.BGM_YEAR = @year"
                            + (job_site_seq == null ? " AND ppr.JOB_SITE_DIRECT_SEQ = @job_site_seq" : "")
                            + (pc_type_seq == null ? " AND ppr.ICM_PC_TYPE_SEQ = @pc_type_seq" : "")
                            + (ppr_type_seq == null ? " AND ppr.ICM_PPR_TYPE_SEQ = @ppr_type_seq" : "")
                            + (ppr_method_seq == null ? " AND ppr.ICM_PPR_METHOD_SEQ = @ppr_method_seq" : "")
                            + (prog_seq == null ? " AND ppr.ICM_PROG_SEQ = @prog_seq" : "")
                            + (record_status == null ? " AND ppr.RECORD_STATUS = @record_status" : " AND RECORD_STATUS <> @delete_status");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        SysPprPriorityInfo infoPprPriority = new SysPprPriorityInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                        SysPprMethodInfo infoPprMethod = new SysPprMethodInfo();
                        SysPprForInfo infoPprFor = new SysPprForInfo();
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        HrmJobSiteInfo infoJobSiteDirect = new HrmJobSiteInfo();
                        FimMoneyRequestInfo infoMoneyRequest = new FimMoneyRequestInfo();
                        HrmEmployeeInfo infoEmployeeRequest = new HrmEmployeeInfo();
                        HrmJobSiteInfo infoJobSiteRequest = new HrmJobSiteInfo();
                        HrmEmployeeInfo infoEmployeeConfirmed = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoEmployeeApproved = new HrmEmployeeInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                        SysProgressInfo infoProgress = new SysProgressInfo();
                        RecordInfo infoRecord = new RecordInfo();
                        SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_SEQ"]);
                        info.Ref_no = (dr[i]["ICM_PPR_NUMBER"]).ToString();
                        info.Name = (dr[i]["ICM_PPR_NAME"]).ToString();
                        info.Reason = (dr[i]["ICM_PPR_REASON"]).ToString();
                        info.YearTypeDescr = (dr[i]["YEAR_TYPE_DESCR"]).ToString();
                        info.Year = (Convert.IsDBNull(dr[i]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[i]["BGM_YEAR"]));
                        info.DateRequested = (Convert.IsDBNull(dr[i]["REQUEST_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUEST_DATE"]));
                        info.DateConfirmed = (Convert.IsDBNull(dr[i]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["CONFIRM_DATE"]));
                        info.DateApproved = (Convert.IsDBNull(dr[i]["APPROVED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["APPROVED_WHEN"]));
                        info.DateRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUIRE_DATE"]));
                        info.DurationRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[i]["REQUIRE_DURATION"]));
                        infoPprPriority.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_PRIOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_PRIOR_SEQ"]));
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_TYPE_SEQ"]));
                        infoPprMethod.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_METHOD_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_METHOD_SEQ"]));
                        infoPprFor.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_FOR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_FOR_SEQ"]));
                        infoPurchaseType.Seq = (Convert.IsDBNull(dr[i]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PC_TYPE_SEQ"]));
                        infoPurchaseType.Name = (dr[i]["ICM_PC_TYPE_NAME"]).ToString();
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["YEAR_TYPE_SEQ"]));
                        infoJobSite.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_SEQ"]));
                        infoJobSiteDirect.Seq = (Convert.IsDBNull(dr[i]["JOB_SITE_DIRECT_SEQ"]) ? null : (Nullable<int>)(dr[i]["JOB_SITE_DIRECT_SEQ"]));
                        infoEmployeeRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_EMP_SEQ"]));
                        infoJobSiteRequest.Seq = (Convert.IsDBNull(dr[i]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_JOB_SITE_SEQ"]));
                        infoEmployeeConfirmed.Seq = (Convert.IsDBNull(dr[i]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[i]["CONFIRM_EMP_SEQ"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_COUNT_SEQ"]));
                        infoProgress.Seq = (Convert.IsDBNull(dr[i]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PROG_SEQ"]));
                        infoStatus.Status = (dr[i]["RECORD_STATUS"]).ToString();
                        infoStatus.Name = (dr[i]["RECORD_STATUS_NAME"]).ToString();
                        infoRecord.Created_by = (Convert.IsDBNull(dr[i]["CREATED_BY"]) ? null : (Nullable<int>)(dr[i]["CREATED_BY"]));
                        infoRecord.Created_when = (Convert.IsDBNull(dr[i]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["CREATED_WHEN"]));
                        infoRecord.Updated_by = (Convert.IsDBNull(dr[i]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[i]["UPDATED_BY"]));
                        infoRecord.Updated_when = (Convert.IsDBNull(dr[i]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["UPDATED_WHEN"]));
                        infoRecord.Restored_by = (Convert.IsDBNull(dr[i]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[i]["RESTORED_BY"]));
                        infoRecord.Restored_when = (Convert.IsDBNull(dr[i]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[i]["RESTORED_WHEN"]));
                        info.InfoPprPriority = infoPprPriority; 
                        info.InfoPprType = infoPprType;
                        info.InfoPprMethod = infoPprMethod; 
                        info.InfoPprFor = infoPprFor;
                        info.InfoPurchaseType = infoPurchaseType; 
                        info.InfoYearType = infoYearType; 
                        info.InfoJobSite = infoJobSite; 
                        info.InfoJobSiteDirect = infoJobSiteDirect;  
                        info.InfoEmployeeRequest = infoEmployeeRequest; 
                        info.InfoJobSiteRequest = infoJobSiteRequest; 
                        info.InfoEmployeeConfirmed = infoEmployeeConfirmed; 
                        info.InfoDurationType = infoDurationType; 
                        info.InfoDurationCount = infoDurationCount;
                        info.InfoProgress = infoProgress; 
                        infoRecord.InfoStatus = infoStatus;
                        info.InfoRecord = infoRecord;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public PrePurchaseRequestInfo GetPrePurchaseRequest(int seq)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT ppr.*"
                            + ", pt.ICM_PC_TYPE_NAME"
                            + ", rs.RECORD_STATUS_NAME"
                            + ", dc.ICM_DUR_COUNT_NAME"
                            + ", dt.ICM_DUR_TYPE_NAME"
                            + ", yt.YEAR_TYPE_NAME"
                            + ", js.JOB_SITE_NAME"
                            + ", um.ICM_PPR_USE_METHOD_NAME"
                            + ", wa.SCOPE_SEQ"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt ON ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN SYS_RECORD_STATUS rs ON ppr.RECORD_STATUS = rs.RECORD_STATUS"
                            + " INNER JOIN SYS_YEAR_TYPE yt ON ppr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_COUNT dc ON ppr.ICM_DUR_COUNT_SEQ = dc.ICM_DUR_COUNT_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_TYPE dt ON ppr.ICM_DUR_TYPE_SEQ = dt.ICM_DUR_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_JOB_SITES js ON js.JOB_SITE_SEQ = ppr.REQUEST_JOB_SITE_SEQ"
                            + " INNER JOIN HRM_MP_WORK_AREAS wa ON js.WORK_AREA_SEQ = wa.WORK_AREA_SEQ"
                            + " INNER JOIN HRM_MP_SCOPES ms ON wa.SCOPE_SEQ = ms.SCOPE_SEQ"
                            + " LEFT JOIN ICM_SYS_PPR_USE_METHOD um ON um.ICM_PPR_USE_METHOD_SEQ = ppr.ICM_PPR_USE_METHOD_SEQ"
                        + " WHERE ppr.ICM_PPR_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    SysPprPriorityInfo infoPprPriority = new SysPprPriorityInfo();
                    SysPprTypeInfo infoPprType = new SysPprTypeInfo();
                    SysPprMethodInfo infoPprMethod = new SysPprMethodInfo();
                    SysPprForInfo infoPprFor = new SysPprForInfo();
                    MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                    SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                    HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                    HrmJobSiteInfo infoJobSiteDirect = new HrmJobSiteInfo();
                    FimMoneyRequestInfo infoMoneyRequest = new FimMoneyRequestInfo();
                    HrmEmployeeInfo infoEmployeeRequest = new HrmEmployeeInfo();
                    HrmJobSiteInfo infoJobSiteRequest = new HrmJobSiteInfo();
                    HrmEmployeeInfo infoEmployeeConfirmed = new HrmEmployeeInfo();
                    HrmEmployeeInfo infoEmployeeApproved = new HrmEmployeeInfo();
                    SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                    SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();
                    SysProgressInfo infoProgress = new SysProgressInfo();
                    SysPprUseMethodInfo infoPprUseMethod = new SysPprUseMethodInfo();
                    RecordInfo infoRecord = new RecordInfo();
                    SysRecordStatusInfo infoStatus = new SysRecordStatusInfo();
                    SysScopeInfo infoHrmScope = new SysScopeInfo();
                    info.Seq = Convert.ToInt32(dr[0]["ICM_PPR_SEQ"]);
                    info.Ref_no = (dr[0]["ICM_PPR_REF_NO"]).ToString();
                    info.Name = (dr[0]["ICM_PPR_NAME"]).ToString();
                    info.Reason = (dr[0]["ICM_PPR_REASON"]).ToString();
                    info.Year = (Convert.IsDBNull(dr[0]["BGM_YEAR"]) ? null : (Nullable<int>)(dr[0]["BGM_YEAR"]));
                    info.DateRequested = (Convert.IsDBNull(dr[0]["REQUEST_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["REQUEST_DATE"]));
                    info.DateConfirmed = (Convert.IsDBNull(dr[0]["CONFIRM_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["CONFIRM_DATE"]));
                    info.DateApproved = (Convert.IsDBNull(dr[0]["APPROVED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["APPROVED_WHEN"]));
                    info.DateRequired = (Convert.IsDBNull(dr[0]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[0]["REQUIRE_DATE"]));
                    info.DurationRequired = (Convert.IsDBNull(dr[0]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[0]["REQUIRE_DURATION"]));
                    infoPprFor.Seq = (Convert.IsDBNull(dr[0]["ICM_PPR_FOR_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PPR_FOR_SEQ"]));
                    infoPurchaseType.Seq = (Convert.IsDBNull(dr[0]["ICM_PC_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PC_TYPE_SEQ"]));
                    infoPurchaseType.Name = (dr[0]["ICM_PC_TYPE_NAME"]).ToString();
                    infoYearType.Seq = (Convert.IsDBNull(dr[0]["YEAR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["YEAR_TYPE_SEQ"]));
                    infoYearType.Name = (dr[0]["YEAR_TYPE_NAME"]).ToString();
                    infoJobSite.Seq = (Convert.IsDBNull(dr[0]["JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[0]["JOB_SITE_SEQ"]));
                    infoJobSite.Name = dr[0]["JOB_SITE_NAME"].ToString();
                    infoJobSiteDirect.Seq = (Convert.IsDBNull(dr[0]["JOB_SITE_DIRECT_SEQ"]) ? null : (Nullable<int>)(dr[0]["JOB_SITE_DIRECT_SEQ"]));
                    infoHrmScope.Seq = (Convert.IsDBNull(dr[0]["SCOPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["SCOPE_SEQ"]));
                    infoJobSiteDirect.InfoScope = infoHrmScope;
                    infoEmployeeRequest.Seq = (Convert.IsDBNull(dr[0]["REQUEST_EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["REQUEST_EMP_SEQ"]));
                    infoJobSiteRequest.Seq = (Convert.IsDBNull(dr[0]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[0]["REQUEST_JOB_SITE_SEQ"]));
                    infoJobSiteRequest.Name = (Convert.IsDBNull(dr[0]["JOB_SITE_NAME"]) ? "" : (dr[0]["JOB_SITE_NAME"]).ToString());
                    infoEmployeeConfirmed.Seq = (Convert.IsDBNull(dr[0]["CONFIRM_EMP_SEQ"]) ? null : (Nullable<int>)(dr[0]["CONFIRM_EMP_SEQ"]));
                    infoDurationType.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_TYPE_SEQ"]));
                    infoDurationType.Name = (dr[0]["ICM_DUR_TYPE_NAME"]).ToString();
                    infoDurationCount.Seq = (Convert.IsDBNull(dr[0]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_DUR_COUNT_SEQ"]));
                    infoDurationCount.Name = (dr[0]["ICM_DUR_COUNT_NAME"]).ToString();
                    infoProgress.Seq = (Convert.IsDBNull(dr[0]["ICM_PROG_SEQ"]) ? null : (Nullable<int>)(dr[0]["ICM_PROG_SEQ"]));
                    infoStatus.Status = (dr[0]["RECORD_STATUS"]).ToString();
                    infoStatus.Name = (dr[0]["RECORD_STATUS_NAME"]).ToString();
                    infoRecord.Created_by = (Convert.IsDBNull(dr[0]["CREATED_BY"]) ? null : (Nullable<int>)(dr[0]["CREATED_BY"]));
                    infoRecord.Created_when = (Convert.IsDBNull(dr[0]["CREATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["CREATED_WHEN"]));
                    infoRecord.Updated_by = (Convert.IsDBNull(dr[0]["UPDATED_BY"]) ? null : (Nullable<int>)(dr[0]["UPDATED_BY"]));
                    infoRecord.Updated_when = (Convert.IsDBNull(dr[0]["UPDATED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["UPDATED_WHEN"]));
                    infoRecord.Restored_by = (Convert.IsDBNull(dr[0]["RESTORED_BY"]) ? null : (Nullable<int>)(dr[0]["RESTORED_BY"]));
                    infoRecord.Restored_when = (Convert.IsDBNull(dr[0]["RESTORED_WHEN"]) ? null : (Nullable<DateTime>)(dr[0]["RESTORED_WHEN"]));
                    infoPprUseMethod.Seq = (Convert.IsDBNull(dr[0]["icm_ppr_use_method_seq"]) ? null : (Nullable<int>)(dr[0]["icm_ppr_use_method_seq"]));
                    infoPprUseMethod.Name = dr[0]["icm_ppr_use_method_name"].ToString();
                    info.InfoPprPriority = infoPprPriority;
                    info.InfoPprType = infoPprType;
                    info.InfoPprMethod = infoPprMethod;
                    info.InfoPprFor = infoPprFor;
                    info.InfoPurchaseType = infoPurchaseType;
                    info.InfoYearType = infoYearType;
                    info.InfoJobSite = infoJobSite;
                    info.InfoJobSiteDirect = infoJobSiteDirect;
                    info.InfoEmployeeRequest = infoEmployeeRequest;
                    info.InfoJobSiteRequest = infoJobSiteRequest;
                    info.InfoEmployeeConfirmed = infoEmployeeConfirmed;
                    info.InfoDurationType = infoDurationType;
                    info.InfoDurationCount = infoDurationCount;
                    info.InfoProgress = infoProgress;
                    info.InfoPprUseMethod = infoPprUseMethod;
                    infoRecord.InfoStatus = infoStatus;
                    info.InfoRecord = infoRecord;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<PrePurchaseRequestInfo> GetCollectRequest()
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._ApproveRecordStatus);

                strSQL = "SELECT CAST(CONCAT(yt.YEAR_TYPE_NAME,' ', ppr.BGM_YEAR) AS CHAR) AS YEAR_TYPE_DESCR"
                        + ", yt.YEAR_TYPE_NAME"
                        + ", ppr.BGM_YEAR"
                        + ", ppr.REQUEST_DATE"
                        + ", TIME(ppr.APPROVED_WHEN) as TIME_SENT"
                        + ", CASE WHEN ppr.ICM_PPR_TYPE_SEQ = 1"
                            + " THEN 'ปกติ' ELSE 'เร่งด่วน'"
                        + " END AS ICM_PPR_TYPE_NAME"
                        + ", ppr.ICM_PPR_TYPE_SEQ"
                        + ", pt.ICM_PC_TYPE_NAME"
                        + ", js.JOB_SITE_NAME"
                        + ", ppr.ICM_PPR_REF_NO"
                        + ", ppr.ICM_PPR_NAME"
                        + ", ppr.ICM_PPR_SEQ"
                        + ", yt.YEAR_TYPE_SEQ"
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr" 
                            + " INNER JOIN SYS_YEAR_TYPE yt on ppr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ "
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt on ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_JOB_SITES js on ppr.REQUEST_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " WHERE ppr.RECORD_STATUS = @status"
                            + " AND ppr.ICM_PPR_SEQ IN "
                                + " (select icm_ppr_seq "
                                + " from icm_pre_purchase_request_item "
                                + " where icm_ppr_seq = ppr.icm_ppr_seq" 
                                    + " and icm_pr_item_seq is null"
                                + ") ";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo(); 
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo(); 
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PPR_SEQ"]);
                        infoYearType.Name = dr[i]["year_type_name"].ToString();
                        info.Year = Convert.ToInt32(dr[i]["bgm_year"]);
                        info.DateRequested = (Convert.IsDBNull(dr[i]["request_date"]) ? null : (Nullable<DateTime>)(dr[i]["request_date"]));
                        info.SendTime = (Convert.IsDBNull(dr[i]["time_sent"]) ? null : (Nullable<TimeSpan>)(dr[i]["time_sent"]));
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["icm_ppr_type_Seq"]) ? null : (Nullable<int>)(dr[i]["icm_ppr_type_Seq"]));
                        infoPprType.Name = dr[i]["icm_ppr_type_name"].ToString();
                        infoPurchaseType.Name = dr[i]["icm_pc_type_name"].ToString();
                        infoJobSite.Name = dr[i]["job_site_name"].ToString();
                        info.Ref_no = dr[i]["icm_ppr_ref_no"].ToString();
                        info.Name = dr[i]["icm_ppr_name"].ToString();
                        info.YearTypeDescr = dr[i]["YEAR_TYPE_DESCR"].ToString();

                        info.InfoYearType = infoYearType;
                        info.InfoPprType = infoPprType;
                        info.InfoPurchaseType = infoPurchaseType;
                        info.InfoJobSite = infoJobSite;

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestInfo> GetCollectRequestGroupItem(Nullable<int> ItemSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoList = new List<PrePurchaseRequestInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@item_seq", ItemSeq);

                strSQL = "select ppr.*"
                        + ", CAST(CONCAT(yt.YEAR_TYPE_NAME,' ', ppr.BGM_YEAR) AS CHAR) AS YEAR_TYPE_DESCR"
                        + ", yt.YEAR_TYPE_NAME"
                        + ", pr.ICM_PC_RULE_NAME "
                        + ", pt.ICM_PC_TYPE_NAME"
                        + ", js.JOB_SITE_NAME"
                        + ", CONCAT(ppr.ICM_PPR_REF_NO, ' - ', ppr.ICM_PPR_NAME) as ICM_PPR_REF_NO_NAME" 
                        + ", ex.BGM_EXP_NAME"
                        + ", res.ICM_RES_ITEM_NAME"
                        + ", pri.ICM_PPR_ITEM_DESCR"
                        + ", pri.ICM_PPR_ITEM_QTY"
                        + ", pri.ICM_PPR_ITEM_PRICE"
                        + ", u.ICM_UNIT_NAME"
                        + ", sdy.ICM_DUR_TYPE_NAME"
                        + ", sdc.ICM_DUR_COUNT_NAME"
                        + ", pri.ICM_PPR_ITEM_SEQ"
                        + ", pr.ICM_PC_RULE_SEQ" 
                        + " FROM ICM_PRE_PURCHASE_REQUEST ppr" 
                            + " INNER JOIN ICM_PRE_PURCHASE_REQUEST_ITEM pri ON ppr.ICM_PPR_SEQ = pri.ICM_PPR_SEQ"
                            + " INNER JOIN BGM_MAS_EXPENSE ex ON pri.BGM_EXP_SEQ = ex.BGM_EXP_SEQ"
                            + " INNER JOIN ICM_MAS_PURCHASE_RULE pr ON pri.ICM_PC_RULE_SEQ = pr.ICM_PC_RULE_SEQ"
                            + " INNER JOIN SYS_YEAR_TYPE yt on ppr.YEAR_TYPE_SEQ = yt.YEAR_TYPE_SEQ "
                            + " INNER JOIN ICM_MAS_PURCHASE_TYPE pt on ppr.ICM_PC_TYPE_SEQ = pt.ICM_PC_TYPE_SEQ"
                            + " INNER JOIN HRM_MP_JOB_SITES js on ppr.REQUEST_JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM res on pri.ICM_RES_ITEM_SEQ = res.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u on res.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_TYPE sdy ON sdy.ICM_DUR_TYPE_SEQ = ppr.ICM_DUR_TYPE_SEQ"
                            + " LEFT JOIN ICM_SYS_DURATION_COUNT sdc ON sdc.ICM_DUR_COUNT_SEQ = ppr.ICM_DUR_COUNT_SEQ"
                        + " WHERE ppr.RECORD_STATUS = @status"
                            + " AND ppr.ICM_PPR_TYPE_SEQ = 1"
                            + " AND pri.ICM_PR_ITEM_SEQ IS NULL"
                            + " AND PPR.ICM_PPR_SEQ IN" 
                                + "(select distinct r.icm_ppr_seq"
                                + " from icm_pre_purchase_request r"
                                    + " inner join icm_pre_purchase_request_item ri on r.icm_ppr_seq = ri.icm_ppr_seq"
                                + " where res.icm_res_item_seq in"
                                    + "	 (select a.icm_res_item_seq" 
                                    + "	from "
                                        + "(select ri.icm_res_item_name, ri.icm_res_item_seq" 
                                        + "	from icm_pre_purchase_request_item pri" 
                                            + "	inner join icm_mas_resource_item ri on pri.icm_res_item_seq = ri.icm_res_item_seq" 
                                        + "	) a" 
                                    + "	group by a.icm_res_item_seq" 
                                    + "	having count(a.icm_res_item_seq) > 1" 
                                    + "	)" 
                                + " ) "
                            + " AND CASE WHEN @item_seq IS NULL THEN 0 = 0 ELSE res.ICM_RES_ITEM_SEQ = @item_seq END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        SysYearTypeInfo infoYearType = new SysYearTypeInfo();
                        MasterPurchaseTypeInfo infoPurchaseType = new MasterPurchaseTypeInfo();
                        HrmJobSiteInfo infoJobSite = new HrmJobSiteInfo();
                        MasterPurchaseRuleInfo infoPurchaseRule = new MasterPurchaseRuleInfo();
                        PrePurchaseRequestItemInfo infoPprItem = new PrePurchaseRequestItemInfo();
                        BgmExpenseInfo infoBgmExpense = new BgmExpenseInfo();
                        SysPprTypeInfo infoPprType = new SysPprTypeInfo(); 
                        ResourceMasterItemInfo infoResourceMasterItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_ppr_seq"]);
                        infoYearType.Seq = (Convert.IsDBNull(dr[i]["year_type_seq"]) ? null : (Nullable<int>)(dr[i]["year_type_seq"]));
                        infoYearType.Name = dr[i]["year_type_name"].ToString();
                        infoPurchaseRule.Seq = (Convert.IsDBNull(dr[i]["icm_pc_rule_seq"]) ? null : (Nullable<int>)(dr[i]["icm_pc_rule_seq"])); 
                        infoPurchaseRule.Name = dr[i]["icm_pc_rule_name"].ToString();
                        infoPurchaseType.Seq = (Convert.IsDBNull(dr[i]["icm_pc_type_seq"]) ? null : (Nullable<int>)(dr[i]["icm_pc_type_seq"]));
                        infoPurchaseType.Name = dr[i]["icm_pc_type_name"].ToString();
                        infoJobSite.Seq = (Convert.IsDBNull(dr[i]["REQUEST_JOB_SITE_SEQ"]) ? null : (Nullable<int>)(dr[i]["REQUEST_JOB_SITE_SEQ"])); 
                        infoJobSite.Name = dr[i]["job_site_name"].ToString();
                        info.Name = dr[i]["icm_ppr_name"].ToString();
                        info.Year = (Convert.IsDBNull(dr[i]["bgm_year"]) ? null : (Nullable<int>)(dr[i]["bgm_year"]));
                        infoBgmExpense.Name = dr[i]["bgm_exp_name"].ToString();
                        infoUnit.Name = dr[i]["icm_unit_name"].ToString();
                        infoPprType.Seq = (Convert.IsDBNull(dr[i]["icm_ppr_type_seq"]) ? null : (Nullable<int>)(dr[i]["icm_ppr_type_seq"]));
                        infoPprItem.Seq = (Convert.IsDBNull(dr[i]["icm_ppr_item_seq"]) ? null : (Nullable<int>)(dr[i]["icm_ppr_item_seq"]));
                        infoPprItem.Name = dr[i]["icm_res_item_name"].ToString();
                        infoPprItem.Descr = dr[i]["icm_ppr_item_descr"].ToString();
                        infoPprItem.Qty = (Convert.IsDBNull(dr[i]["icm_ppr_item_qty"]) ? null : (Nullable<int>)(dr[i]["icm_ppr_item_qty"]));
                        infoPprItem.Price = (Convert.IsDBNull(dr[i]["icm_ppr_item_price"]) ? null : (Nullable<double>)(dr[i]["icm_ppr_item_price"]));
                        info.DateRequested = (Convert.IsDBNull(dr[i]["request_date"]) ? null : (Nullable<DateTime>)(dr[i]["request_date"]));
                        info.DateRequired = (Convert.IsDBNull(dr[i]["require_date"]) ? null : (Nullable<DateTime>)(dr[i]["require_date"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationType.Name = dr[i]["icm_dur_type_name"].ToString();
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_COUNT_SEQ"]));
                        infoDurationCount.Name = dr[i]["icm_dur_count_name"].ToString();
                        info.DurationRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[i]["REQUIRE_DURATION"]));
                        info.YearTypeDescr = dr[i]["YEAR_TYPE_DESCR"].ToString();
                        info.RefNoName = dr[i]["ICM_PPR_REF_NO_NAME"].ToString();

                        info.InfoYearType = infoYearType;
                        info.InfoPurchaseRule = infoPurchaseRule;
                        info.InfoPurchaseType = infoPurchaseType;
                        info.InfoJobSite = infoJobSite;
                        infoResourceMasterItem.InfoUnit = infoUnit;
                        info.InfoResourceMasterItem = infoResourceMasterItem;
                        infoPprItem.InfoExpense = infoBgmExpense;
                        info.InfoPprItem = infoPprItem;
                        info.InfoPprType = infoPprType;
                        info.InfoDurationType = infoDurationType;
                        info.InfoDurationCount = infoDurationCount;
                        
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PrePurchaseRequestInfo> GetDuration(IList<PrePurchaseRequestInfo> infoList)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoPprList = new List<PrePurchaseRequestInfo>();
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();

                strSQL = "SELECT DISTINCT ppr.REQUIRE_DATE"
                        + ", ppr.ICM_DUR_TYPE_SEQ"
                        + ", sdy.ICM_DUR_TYPE_NAME"
                        + ", ppr.ICM_DUR_COUNT_SEQ"
                        + ", sdc.ICM_DUR_COUNT_NAME"
                        + ", ppr.REQUIRE_DURATION"
                    + " FROM ICM_PRE_PURCHASE_REQUEST ppr"
                        + " LEFT JOIN ICM_SYS_DURATION_TYPE sdy ON sdy.ICM_DUR_TYPE_SEQ = ppr.ICM_DUR_TYPE_SEQ"
                        + " LEFT JOIN ICM_SYS_DURATION_COUNT sdc ON sdc.ICM_DUR_COUNT_SEQ = ppr.ICM_DUR_COUNT_SEQ"
                    + " WHERE ppr.ICM_PPR_SEQ IN (" + seq + ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();
                        SysDurationTypeInfo infoDurationType = new SysDurationTypeInfo();
                        SysDurationCountInfo infoDurationCount = new SysDurationCountInfo();

                        info.DateRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DATE"]) ? null : (Nullable<DateTime>)(dr[i]["REQUIRE_DATE"]));
                        infoDurationType.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_TYPE_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_TYPE_SEQ"]));
                        infoDurationType.Name = dr[i]["ICM_DUR_TYPE_NAME"].ToString();
                        infoDurationCount.Seq = (Convert.IsDBNull(dr[i]["ICM_DUR_COUNT_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_DUR_COUNT_SEQ"]));
                        infoDurationCount.Name = dr[i]["ICM_DUR_COUNT_NAME"].ToString();
                        info.DurationRequired = (Convert.IsDBNull(dr[i]["REQUIRE_DURATION"]) ? null : (Nullable<int>)(dr[i]["REQUIRE_DURATION"]));

                        info.InfoDurationType = infoDurationType;
                        info.InfoDurationCount = infoDurationCount;

                        infoPprList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoPprList;
        }

        public IList<PrePurchaseRequestInfo> GetDistinctSeq(IList<PrePurchaseRequestInfo> infoList)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoPprList = new List<PrePurchaseRequestInfo>();
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();

                strSQL = "SELECT DISTINCT ICM_PPR_SEQ"
                    + " FROM ICM_PRE_PURCHASE_REQUEST"
                    + " WHERE ICM_PPR_SEQ IN (" + seq + ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();

                        info.Seq = (Convert.IsDBNull(dr[i]["ICM_PPR_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_PPR_SEQ"])); ;

                        infoPprList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoPprList;
        }

        public IList<PrePurchaseRequestInfo> GetName(IList<PrePurchaseRequestInfo> infoList)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoPprList = new List<PrePurchaseRequestInfo>();
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();

                strSQL = "SELECT DISTINCT ICM_PPR_NAME"
                    + " FROM ICM_PRE_PURCHASE_REQUEST"
                    + " WHERE ICM_PPR_SEQ IN (" + seq + ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();

                        info.Name = dr[i]["ICM_PPR_NAME"].ToString();

                        infoPprList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoPprList;
        }

        public IList<PrePurchaseRequestInfo> GetReason(IList<PrePurchaseRequestInfo> infoList)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PrePurchaseRequestInfo> infoPprList = new List<PrePurchaseRequestInfo>();
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();

                strSQL = "SELECT DISTINCT ICM_PPR_REASON"
                    + " FROM ICM_PRE_PURCHASE_REQUEST"
                    + " WHERE ICM_PPR_SEQ IN (" + seq + ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestInfo info = new PrePurchaseRequestInfo();

                        info.Reason = dr[i]["ICM_PPR_REASON"].ToString();

                        infoPprList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoPprList;
        }

        public void Insert(PrePurchaseRequestInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                //dbManager.AddParameters(0, "@code", info.Code);
                //dbManager.AddParameters(1, "@name", info.Name);
                //dbManager.AddParameters(2, "@descr", info.Descr);
                //dbManager.AddParameters(3, "@record_status", SystemInfo._WorkRecordStatus);
                //dbManager.AddParameters(4, "@created_by", info.InfoRecord.Created_by);
                //dbManager.AddParameters(5, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_MAS_UNIT"
                    + " (ICM_UNIT_CODE, ICM_UNIT_NAME, ICM_UNIT_DESCR"
                    + " , RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@code"
                    + ", @name"
                    + ", @descr"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string Insert(PrePurchaseRequestInfo info, IList<PrePurchaseRequestItemInfo> infoListItem, IList<PrePurchaseRequestCommitteeInfo> infoListCommittee, IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            string lastNumber = "";
            PrePurchaseRequestItemDAL dalItem = new PrePurchaseRequestItemDAL();
            PrePurchaseRequestCommitteeDAL dalCommittee = new PrePurchaseRequestCommitteeDAL();
            DocumentDAL dalDocument = new DocumentDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@year_type", info.InfoYearType.Seq);
                dbManager.AddParameters(1, "@year", info.Year);
                dbManager.AddParameters(2, "@request_job_site_seq", info.InfoJobSiteRequest.Seq);
                dbManager.AddParameters(3, "@job_site_seq", info.InfoJobSite.Seq);
                dbManager.AddParameters(4, "@job_site_direct_seq", info.InfoJobSiteDirect.Seq);
                dbManager.AddParameters(5, "@request_emp_seq", info.InfoEmployeeRequest.Seq);
                dbManager.AddParameters(6, "@request_date", info.DateRequested);
                dbManager.AddParameters(7, "@pc_type_seq", info.InfoPurchaseType.Seq);
                dbManager.AddParameters(8, "@name", info.Name);
                dbManager.AddParameters(9, "@reason", info.Reason);
                dbManager.AddParameters(10, "@for_seq", info.InfoPprFor.Seq);
                dbManager.AddParameters(11, "@type_seq", info.InfoPprType.Seq);
                dbManager.AddParameters(12, "@dur_req", info.DurationRequired);
                dbManager.AddParameters(13, "@dur_type_seq", info.InfoDurationType.Seq);
                dbManager.AddParameters(14, "@dur_count_seq", info.InfoDurationCount.Seq);
                dbManager.AddParameters(15, "@date_req", info.DateRequired);
                dbManager.AddParameters(16, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(17, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(18, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(19, "@request_month", info.DateRequested.Value.Month);
                dbManager.AddParameters(20, "@request_year", info.DateRequested.Value.Year);
                dbManager.AddParameters(21, "@use_method", info.InfoPprUseMethod.Seq);
                //dbManager.AddParameters(21, "@prior_seq", info.InfoPprPriority.Seq);
                //dbManager.AddParameters(22, "@method_seq", info.InfoPprMethod.Seq);
                //dbManager.AddParameters(23, "@money_seq", info.InfoMoneyRequest.Seq);


                strSQL = "INSERT INTO ICM_PRE_PURCHASE_REQUEST"
                    + " (YEAR_TYPE_SEQ, BGM_YEAR, REQUEST_JOB_SITE_SEQ, JOB_SITE_SEQ, JOB_SITE_DIRECT_SEQ, REQUEST_EMP_SEQ"
                    + ", REQUEST_DATE, ICM_PC_TYPE_SEQ, ICM_PPR_NAME, ICM_PPR_REASON"
                    + ", ICM_PPR_FOR_SEQ, ICM_PPR_TYPE_SEQ, REQUIRE_DURATION"
                    + ", ICM_DUR_TYPE_SEQ, ICM_DUR_COUNT_SEQ, REQUIRE_DATE, ICM_PPR_REF_NO, ICM_PPR_USE_METHOD_SEQ"
                    + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@year_type"
                    + ", @year"
                    + ", @request_job_site_seq"
                    + ", @job_site_seq"
                    + ", @job_site_direct_seq"
                    + ", @request_emp_seq"
                    + ", @request_date"
                    + ", @pc_type_seq"
                    + ", @name"
                    + ", @reason"
                    + ", @for_seq"
                    + ", @type_seq"
                    + ", @dur_req"
                    + ", @dur_type_seq"
                    + ", @dur_count_seq"
                    + ", @date_req"
                    + ", CONCAT('PPR-', @request_year + 543, right(concat('00',@request_month),2), '-'"
                        + ", (select right("
                            + " concat('0000'"
                                + ", coalesce("
                                    + "right("
                                        + "(select max(sub.icm_ppr_ref_no)"
                                        + " from icm_pre_purchase_request sub"
                                        + " where month(sub.request_date) = @request_month"
                                            + " and year(sub.request_date) = @request_year"
                                        + ")"
                                    + ", 4)"
                                + ", 0)"
                            + " + 1)"
                        + ",4)"
                    + "))"
                    + ", @use_method"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM ICM_PRE_PURCHASE_REQUEST";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                dalItem.Insert(infoListItem, lastSeq, dbManager);
                dalCommittee.Insert(infoListCommittee, lastSeq, dbManager);
                dalDocument.InsertFileToDB(infoListFile, "ICM_PRE_PURCHASE_REQUEST_FILE", "ICM_PPR_SEQ", lastSeq, dbManager);

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@lastSeq", lastSeq);
                
                strSQL = "SELECT ICM_PPR_REF_NO"
                    + " FROM ICM_PRE_PURCHASE_REQUEST"
                    + " WHERE ICM_PPR_SEQ = @lastSeq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }

                dbManager.CommitTransaction();
            }
            catch(Exception ex)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return lastNumber;
        }

        public string Insert(PrePurchaseRequestInfo info, IList<PrePurchaseRequestItemInfo> infoListItem, IList<DocumentInfo> infoListFile)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            string lastNumber = "";
            PrePurchaseRequestItemDAL dalItem = new PrePurchaseRequestItemDAL();
            PrePurchaseRequestCommitteeDAL dalCommittee = new PrePurchaseRequestCommitteeDAL();
            DocumentDAL dalDocument = new DocumentDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(22);

                dbManager.AddParameters(0, "@year_type", info.InfoYearType.Seq);
                dbManager.AddParameters(1, "@year", info.Year);
                dbManager.AddParameters(2, "@request_job_site_seq", info.InfoJobSiteRequest.Seq);
                dbManager.AddParameters(3, "@job_site_seq", info.InfoJobSite.Seq);
                dbManager.AddParameters(4, "@job_site_direct_seq", info.InfoJobSiteDirect.Seq);
                dbManager.AddParameters(5, "@request_emp_seq", info.InfoEmployeeRequest.Seq);
                dbManager.AddParameters(6, "@request_date", info.DateRequested);
                dbManager.AddParameters(7, "@pc_type_seq", info.InfoPurchaseType.Seq);
                dbManager.AddParameters(8, "@name", info.Name);
                dbManager.AddParameters(9, "@reason", info.Reason);
                dbManager.AddParameters(10, "@for_seq", info.InfoPprFor.Seq);
                dbManager.AddParameters(11, "@type_seq", info.InfoPprType.Seq);
                dbManager.AddParameters(12, "@dur_req", info.DurationRequired);
                dbManager.AddParameters(13, "@dur_type_seq", info.InfoDurationType.Seq);
                dbManager.AddParameters(14, "@dur_count_seq", info.InfoDurationCount.Seq);
                dbManager.AddParameters(15, "@date_req", info.DateRequired);
                dbManager.AddParameters(16, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(17, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(18, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(19, "@request_month", info.DateRequested.Value.Month);
                dbManager.AddParameters(20, "@request_year", info.DateRequested.Value.Year);
                dbManager.AddParameters(21, "@use_method", info.InfoPprUseMethod.Seq);

                strSQL = "INSERT INTO ICM_PRE_PURCHASE_REQUEST"
                    + " (YEAR_TYPE_SEQ, BGM_YEAR, REQUEST_JOB_SITE_SEQ, JOB_SITE_SEQ, JOB_SITE_DIRECT_SEQ, REQUEST_EMP_SEQ"
                    + ", REQUEST_DATE, ICM_PC_TYPE_SEQ, ICM_PPR_NAME, ICM_PPR_REASON"
                    + ", ICM_PPR_FOR_SEQ, ICM_PPR_TYPE_SEQ, REQUIRE_DURATION"
                    + ", ICM_DUR_TYPE_SEQ, ICM_DUR_COUNT_SEQ, REQUIRE_DATE, ICM_PPR_REF_NO"
                    + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN, icm_ppr_use_method_seq)"
                    + " VALUES(@year_type"
                    + ", @year"
                    + ", @request_job_site_seq"
                    + ", @job_site_seq"
                    + ", @job_site_direct_seq"
                    + ", @request_emp_seq"
                    + ", @request_date"
                    + ", @pc_type_seq"
                    + ", @name"
                    + ", @reason"
                    + ", @for_seq"
                    + ", @type_seq"
                    + ", @dur_req"
                    + ", @dur_type_seq"
                    + ", @dur_count_seq"
                    + ", @date_req"
                    + ", CONCAT('PPR-', @request_year + 543, right(concat('00',@request_month),2), '-'"
                        + ", (select right("
                            + " concat('0000'"
                                + ", coalesce("
                                    + "right("
                                        + "(select max(sub.icm_ppr_ref_no)"
                                        + " from icm_pre_purchase_request sub"
                                        + " where month(sub.request_date) = @request_month"
                                            + " and year(sub.request_date) = @request_year"
                                        + ")"
                                    + ", 4)"
                                + ", 0)"
                            + " + 1)"
                        + ",4)"
                    + "))"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + ", @use_method"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM ICM_PRE_PURCHASE_REQUEST";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                }

                dalItem.Insert(infoListItem, lastSeq, dbManager);
                dalDocument.InsertFileToDB(infoListFile, "ICM_PRE_PURCHASE_REQUEST_FILE", "ICM_PPR_SEQ", lastSeq, dbManager);

                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@lastSeq", lastSeq);

                strSQL = "SELECT ICM_PPR_REF_NO"
                    + " FROM ICM_PRE_PURCHASE_REQUEST"
                    + " WHERE ICM_PPR_SEQ = @lastSeq";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }


                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return lastNumber;
        }

        public void Update(PrePurchaseRequestInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@request_job_site_seq", info.InfoJobSiteRequest.Seq);
                dbManager.AddParameters(2, "@job_site_seq", info.InfoJobSite.Seq);
                dbManager.AddParameters(3, "@job_site_direct_seq", info.InfoJobSiteDirect.Seq);
                dbManager.AddParameters(4, "@request_emp_seq", info.InfoEmployeeRequest.Seq);
                dbManager.AddParameters(5, "@request_date", info.DateRequested);
                dbManager.AddParameters(6, "@pc_type_seq", info.InfoPurchaseType.Seq);
                dbManager.AddParameters(7, "@name", info.Name);
                dbManager.AddParameters(8, "@reason", info.Reason);
                dbManager.AddParameters(9, "@for_seq", info.InfoPprFor.Seq);
                dbManager.AddParameters(10, "@dur_req", info.DurationRequired);
                dbManager.AddParameters(11, "@dur_type_seq", info.InfoDurationType.Seq);
                dbManager.AddParameters(12, "@dur_count_seq", info.InfoDurationCount.Seq);
                dbManager.AddParameters(13, "@date_req", info.DateRequired);
                dbManager.AddParameters(14, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(15, "@updated_when", info.InfoRecord.Updated_when);
                dbManager.AddParameters(16, "@year_type_seq", info.InfoYearType.Seq);
                dbManager.AddParameters(17, "@bgm_year", info.Year);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                    + " SET REQUEST_JOB_SITE_SEQ = @request_job_site_seq"
                    + ", JOB_SITE_SEQ = @job_site_seq"
                    + ", JOB_SITE_DIRECT_SEQ = @job_site_direct_seq"
                    + ", REQUEST_EMP_SEQ = @request_emp_seq"
                    + ", REQUEST_DATE = @request_date"
                    + ", ICM_PC_TYPE_SEQ = @pc_type_seq"
                    + ", ICM_PPR_NAME = @name"
                    + ", ICM_PPR_REASON = @reason"
                    + ", ICM_PPR_FOR_SEQ = @for_seq"
                    + ", REQUIRE_DURATION = @dur_req"
                    + ", ICM_DUR_TYPE_SEQ = @dur_type_seq"
                    + ", ICM_DUR_COUNT_SEQ = @dur_count_seq"
                    + ", REQUIRE_DATE = @date_req"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + ", YEAR_TYPE_SEQ = @year_type_seq"
                    + ", BGM_YEAR = @bgm_year"
                    + " WHERE ICM_PPR_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateStatus(int PprSeq,string RecordStatus,int UpdateBy, IDBManager dbManagerTrans)
        {
            try
            {
                dbManagerTrans.CreateParameters(4);
                dbManagerTrans.AddParameters(0, "@icm_ppr_seq", PprSeq);
                dbManagerTrans.AddParameters(1, "@record_status", RecordStatus);
                dbManagerTrans.AddParameters(2, "@updated_by", UpdateBy);
                dbManagerTrans.AddParameters(3, "@updated_when", DateTime.Now);
                string strSql;
                strSql = "update icm_pre_purchase_request set record_status = @record_status, updated_by = @updated_by,updated_when = @updated_when where icm_ppr_seq = @icm_ppr_seq";
                dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Delete(PrePurchaseRequestInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(3, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PPR_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteAll(IList<PrePurchaseRequestInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", infoList[0].InfoRecord.Updated_by);
                dbManager.AddParameters(2, "@updated_when", infoList[0].InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                    + " SET RECORD_STATUS = @record_status"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                    + " WHERE ICM_PPR_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteFromDB(IList<PrePurchaseRequestInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_PRE_PURCHASE_REQUEST"
                    + " WHERE ICM_PPR_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Restore(IList<PrePurchaseRequestInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(1, "@restored_by", infoList[0].InfoRecord.Restored_by);
                dbManager.AddParameters(2, "@restored_when", infoList[0].InfoRecord.Restored_when);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                    + " SET RECORD_STATUS = @record_status"
                    + ", RESTORED_BY = @restored_by"
                    + ", RESTORED_WHEN = @restored_when"
                    + " WHERE ICM_PPR_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Confirm(PrePurchaseRequestInfo info, IList<PrePurchaseRequestRouteInfo> infoListRoute)
        {
            string strSQL;
            PrePurchaseRequestRouteDAL dalRoute = new PrePurchaseRequestRouteDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@confirm_emp_seq", info.InfoEmployeeConfirmed.Seq);
                dbManager.AddParameters(2, "@confirm_date", info.DateConfirmed);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                    + " SET CONFIRM_EMP_SEQ = @confirm_emp_seq"
                    + ", CONFIRM_DATE = @confirm_date"
                    + ", RECORD_STATUS = @record_status"
                    + " WHERE ICM_PPR_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@created_by", info.InfoEmployeeConfirmed.Seq);
                dbManager.AddParameters(2, "@created_when", DateTime.Now);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                strSQL = "INSERT INTO BGM_PROJECT_OPERATION_REPORT"
                    + "(DESCR, BGM_PROJ_OPER_EXP_SEQ, ICM_PPR_ITEM_SEQ, USED_AMOUNT, CAL_AMOUNT, RPT_TYPE, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + "(SELECT (select icm_ppr_name from icm_pre_purchase_request where icm_ppr_seq = @seq), BGM_PROJ_OPER_EXP_SEQ, ICM_PPR_ITEM_SEQ, COALESCE((ICM_PPR_ITEM_QTY * ICM_PPR_ITEM_PRICE),0)"
                        + ", COALESCE(- (ICM_PPR_ITEM_QTY * ICM_PPR_ITEM_PRICE),0), 4, @record_status, @created_by, @created_when"
                    + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM"
                    + " WHERE ICM_PPR_SEQ = @seq)";
                
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                //dalRoute.Insert(infoListRoute, (int)info.Seq, (int)info.InfoEmployeeConfirmed.Seq, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Approved(PrePurchaseRequestInfo info, IList<PrePurchaseRequestRouteInfo> infoListRoute)
        {
            string strSQL;
            PrePurchaseRequestRouteDAL dalRoute = new PrePurchaseRequestRouteDAL();
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@confirm_emp_seq", info.InfoEmployeeConfirmed.Seq);
                dbManager.AddParameters(2, "@confirm_date", info.DateConfirmed);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                strSQL = "UPDATE ICM_PRE_PURCHASE_REQUEST"
                    + " SET CONFIRM_EMP_SEQ = @confirm_emp_seq"
                    + ", CONFIRM_DATE = @confirm_date"
                    + ", RECORD_STATUS = @record_status"
                    + " WHERE ICM_PPR_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //dbManager.CreateParameters(4);

                //dbManager.AddParameters(0, "@seq", info.Seq);
                //dbManager.AddParameters(1, "@created_by", info.InfoEmployeeConfirmed.Seq);
                //dbManager.AddParameters(2, "@created_when", DateTime.Now);
                //dbManager.AddParameters(3, "@record_status", SystemInfo._ApproveRecordStatus);

                //strSQL = "INSERT INTO BGM_PROJECT_OPERATION_REPORT"
                //    + "(DESCR, BGM_PROJ_OPER_EXP_SEQ, ICM_PPR_ITEM_SEQ, USED_AMOUNT, CAL_AMOUNT, RPT_TYPE, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                //    + "(SELECT (select icm_ppr_name from icm_pre_purchase_request where icm_ppr_seq = @seq), BGM_PROJ_OPER_EXP_SEQ, ICM_PPR_ITEM_SEQ, COALESCE((ICM_PPR_ITEM_QTY * ICM_PPR_ITEM_PRICE),0)"
                //        + ", COALESCE(- (ICM_PPR_ITEM_QTY * ICM_PPR_ITEM_PRICE),0), 4, @record_status, @created_by, @created_when"
                //    + " FROM ICM_PRE_PURCHASE_REQUEST_ITEM"
                //    + " WHERE ICM_PPR_SEQ = @seq)";

                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                //dalRoute.Insert(infoListRoute, (int)info.Seq, (int)info.InfoEmployeeConfirmed.Seq, dbManager);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public IList<HrmEmployeeInfo> GetPrePurchaseRequestOperationName(int pprTypeSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<HrmEmployeeInfo> infoList = new List<HrmEmployeeInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_ppr_type_seq", pprTypeSeq);

                strSQL = "select distinct r.purchase_emp_seq" +
                        " ,  concat(p.prefix_name, e.first_name, ' ' ,e.last_name) as purchase_emp" +
                        " from icm_purchase_request r" +
                        " inner join hrm_ps_employees e on r.purchase_emp_seq = e.emp_seq" +
                        " inner join mas_name_prefixes p on e.name_prefix_seq = p.name_prefix_seq" +
                        " where r.record_status <> 'D' and r.icm_ppr_type_seq = @icm_ppr_type_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        HrmEmployeeInfo info = new HrmEmployeeInfo();

                        info.Seq = (Convert.IsDBNull(dr[i]["purchase_emp_seq"]) ? null : (Nullable<int>)(dr[i]["purchase_emp_seq"]));
                        info.Name = dr[i]["purchase_emp"].ToString();

                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public void SendToPurchaseRequest(int PprSeq, DateTime pr_date, int PrEmpSeq)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ppr_seq", PprSeq);
                dbManager.AddParameters(1, "@pr_date", pr_date);
                dbManager.AddParameters(2, "@pr_emp_seq", PrEmpSeq);

                strSQL = "CALL P_Auto_Insert_Purchase_Request(@ppr_seq,@pr_date,@pr_emp_seq)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


    }
}
